import os
import xlrd
import xlwt
import time
from service.Message import Message,CodeMessageData
class XlsHelper():
    uploadDir='./static/upload'
    downloadDir='./static/download'
    tableList=['tb_user', 'tb_personal_team', 'tb_exchange', 'tb_subject', 'tb_project', 'tb_teaching_research', 'tb_patent', 'tb_paper', 'tb_book']
    tableDic = {
        'tb_user': '用户表',
        'tb_personal_team': '个人或团队荣誉',
        'tb_exchange': '培训进修、交流',
        'tb_subject': '学科竞赛',
        'tb_project': '项目',
        'tb_teaching_research': '教学科研学奖',
        'tb_patent': '专利',
        'tb_paper': '论文',
        'tb_book': '著作'
    }
    xlsDic = {
        'tb_user': [ 'job_number', 'user_name',  'access_level'],#'password'
        'tb_personal_team': [ 'job_number', 'title', 'honor_type', 'name', 'time', 'rewards_bureau', 'theme'],
        'tb_exchange': [ 'job_number', 'teacher_name', 'train_type', 'start_time', 'end_time', 'content', 'theme'],
        'tb_subject': [ 'job_number', 'competition_name', 'teams', 'award_level', 'advisor', 'remark',
                       'certificate_copy', 'number'],
        'tb_project': [ 'job_number', 'presenter_flag', 'member', 'project_name', 'fee', 'project_number',
                       'remark', 'project_start_time', 'project_end_time', 'project_type', 'order', 'support_unit'],
        'tb_teaching_research': [ 'job_number', 'result_name', 'result_form', 'units_issued', 'award_type', 'award_grade', 'award_year', 'order', 'total_people'],
        'tb_patent': [ 'job_number', 'result_name', 'result_form', 'units_issued', 'patent_type', 'patent_number', 'patent_year', 'award_time', 'order', 'total_people'],
        'tb_paper': ['job_number', 'result_name', 'result_form', 'publication_name', 'issuing_time', 'isbn_number', 'publication_level', 'search_info', 'order', 'total_people'],
        'tb_book': [ 'job_number', 'result_name', 'result_form', 'press_name', 'press_time', 'press_count',
                    'isbn_number', 'press_level', 'author_is_eic', 'own_words', 'total_words']
    }
    xlsDescription = {
        'tb_user': [ '工号', '用户名', '权限'], #'密码'
        'tb_personal_team': [ '工号', '称号', '类别', '名称', '时间', '颁奖单位', '主题'],
        'tb_exchange': [ '工号', '教师姓名', '培训进修、交流类型', '开始时间', '结束时间', '培训内容', '主题'],
        'tb_subject': [ '工号', '竞赛名称', '参赛队', '获奖等级', '指导教师', '备注', '证书复印件', '编号'],
        'tb_project': [ '工号', '是否是主持人', '参与人', '项目名称', '经费', '项目编号', '备注', '项目开始时间', '结题时间', '项目类别', '排序', '支持单位'],
        'tb_teaching_research':['工号', '成果名称', '成果形式', '颁奖单位', '奖励类别', '奖励等次', '奖励年度及颁奖时间', '本人序次', '总人数；所有获奖人列表（用逗号分隔）'],
        'tb_patent':[ '工号', '成果名称', '成果形式', '颁发单位', '专利或权属类别', '专利或权属编号', '专利或权属年度', '颁发时间', '本人序次', '总人数；所有权属人列表（用逗号分隔）'],
        'tb_paper': ['工号', '成果名称', '成果形式', '刊物名称', '发表时间', 'ISSN/ISBN编号', '刊物级别', '检索收录情况', '作者排序', '作者总数；所有作者列表（用逗号分隔）'],
        'tb_book': [ '工号', '成果名称', '成果形式', '出版社名称', '出版时间', '出版版次', 'ISBN编号', '出版级别', '作者是否主编', '本人承担字数', '总字数']
    }

    def __init__(self):
        print('XlsHelper')


    def openXls(self,filePath,sheetIndex=0):
        """
        only open a sheet
        :param filePath: xlsData
        :param sheetIndex: default open sheet 0
        :return: sheet(Index of sheet  is sheetIndex)
        """
        xlsxData=xlrd.open_workbook(filePath)
        sheet=xlsxData.sheets()[sheetIndex]
        return sheet

    def readRowsAndCols(self,sheet):
        """
        :param sheet: sheet
        :return: rows and cols
        """
        rows=sheet.nrows
        cols=sheet.ncols
        return  rows,cols

    def getValues(self,sheet,row,col):
        """
        :param sheet: sheet
        :param row: row >=0
        :param col: col >=0
        :return: (row,col)   values
        """
        return sheet.cell_value(row,col)

    def getMoreValues(self,sheet,row,col):
        """
        :param sheet: sheet
        :param row: row >=0
        :param col: col >=0
        :return: (row,col) values
        """
        return sheet.cell(0,1)

    def getRowData(self,sheet,index):
        """
        :param sheet: sheet
        :param index: index
        :return: rowData
        """
        rowData=sheet.row_values(index)
        return rowData

    def getColData(self,sheet,index):
        """
        :param sheet: sheet
        :param index: index
        :return: colData
        """
        colData=sheet.col_values(index)
        return colData

    def readAllData(self,sheet):
        """
        :param sheet: sheet
        :return: allData
        """
        rows = sheet.nrows
        cols = sheet.ncols
        allData=[]
        for row in range(rows):
            part=[]
            for col in range(cols):
                part.append(sheet.cell_value(row, col))
            allData.append(part)
        return allData

    def readAllDataByType(self,sheet,excelType,fileList=[]):
        """
        read all data by excel's type
        :param sheet:
        :param excelType:
        :return:
        """
        dicAllData = []
        try:
            rows = sheet.nrows
            cols = sheet.ncols
            allData = []
            for row in range(rows):
                part = []
                for col in range(cols):
                    part.append(sheet.cell_value(row, col))
                allData.append(part)

            firstLine = self.xlsDic[self.tableList[excelType]]
            code = 0000
            if (len(fileList) == 0):
                for each in allData:
                    part = {}
                    for index, value in enumerate(each):
                        part[firstLine[index]] = value
                    dicAllData.append(part)
            elif (len(firstLine) == len(fileList)) or ((len(firstLine) + 1) == len(fileList) and excelType == 0):
                for each in allData:
                    part = {}
                    for index, value in enumerate(each):
                        part[fileList[index]] = value
                    dicAllData.append(part)
            else:
                code = 2004
        except:
            code=2008
        return Message(code,CodeMessageData[code],dicAllData)



    def writeXls(self,allData,tableName,path='./'):
        """
        default sheet is sheet1
        :param allData:
        :param tableName:
        :return:
        """
        fileObject = xlwt.Workbook(encoding='utf-8', style_compression=0)
        newSheet = fileObject.add_sheet('sheet1', cell_overwrite_ok=True)
        rows = len(allData)
        cols = len(allData[0])
        for row in range(rows):
            for col in range(cols):
                newSheet.write(row, col, allData[row][col])
                # print(type(allData[row][col]))
        fileObject.save(path+tableName)
        return 'sucess'

    def writeXlsByType(self,dicData,saveDir,tableName,excelType=-1):
        """
        :param allData:
        :param tableName:
        :param excelType:
        :return:
        """
        data=[]
        if(excelType>-1 and excelType<9):
            savePath=os.path.join(saveDir,tableName)
            fileObject = xlwt.Workbook(encoding='utf-8', style_compression=0)
            newSheet = fileObject.add_sheet('sheet1', cell_overwrite_ok=True)
            code = 0000
            allData = []
            firstLine = self.xlsDic[self.tableList[excelType]]

            for each in dicData:
                part=[]
                for i in firstLine:
                    part.append(each[i])
                allData.append(part)
            firstDes=self.xlsDescription[self.tableList[excelType]]
            allData = [firstDes] + allData
            rows = len(allData)
            print(allData)
            cols = len(allData[0])
            for row in range(rows):
                for col in range(cols):
                    newSheet.write(row, col, allData[row][col])
                # print(type(allData[row][col]))
            fileObject.save(savePath)
            data=[savePath]
        else:
            data=[]
            code=2005
        return Message(code,CodeMessageData[code],data=data)

    def getNameByExcelType(self,excelType=-1):

        """
        :param excelType:excel type
        :return:
        """
        if (excelType > -1 and excelType < 9):
            tableName=self.tableDic[self.tableList[excelType]]+"_"+str(int(time.time()))+".xls"
        else:
            tableName="unknown"+"_"+str(int(time.time()))+".xls"
        return tableName
